package com.jky.trial.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.jky.trial.common.ExportUtil;
import com.jky.trial.common.Utils;
import com.jky.trial.constant.DataStatus;
import com.jky.trial.entity.Task;
import com.jky.trial.service.ExportService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导出实现类
 * Created by suweiming on 2017/10/5.
 */
@Service
public class ExportServiceImpl implements ExportService {
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    @Override
    public List importLawLeaderExcel(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            if (r.getCell(0) != null) jsonObject.put("comId", getCellValue(r.getCell(0)));
            if (r.getCell(1) != null) jsonObject.put("region", getCellValue(r.getCell(1)));
            if (r.getCell(2) != null) jsonObject.put("org", getCellValue(r.getCell(2)));
            if (r.getCell(3) != null) jsonObject.put("name", getCellValue(r.getCell(3)));
            if (r.getCell(4) != null) jsonObject.put("phone", getCellValue(r.getCell(4)));
            if (r.getCell(5) != null) jsonObject.put("location", getCellValue(r.getCell(5)));
            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List imporEarlyWarning(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            if (r.getCell(0) != null) jsonObject.put("name", getCellValue(r.getCell(0)));
            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List imporLawDomain(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            if (r.getCell(0) != null) jsonObject.put("name", getCellValue(r.getCell(0)));
            if (r.getCell(1) != null) jsonObject.put("grade", getCellValue(r.getCell(1)));
            if (r.getCell(2) != null) jsonObject.put("org", getCellValue(r.getCell(2)));
            if (r.getCell(3) != null) jsonObject.put("domain", getCellValue(r.getCell(3)));
            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List<Task> importTaskExcel(InputStream inputStream, Integer projectId, Integer userId) throws IOException {
        List<Task> temp = new ArrayList();
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            if (r.getRowNum() == 0) {//行号1跳过
                continue;
            }
            if (r.getCell(1)!=null&&!"".equals(r.getCell(1).getStringCellValue())) {
                Task task = new Task();
                task.setCreatorId(userId);
                //取出当前行第1个(默认从0开始)单元格数据，并封装
                if (r.getCell(0) != null) task.setNo(getCellValue(r.getCell(0)));
                if (r.getCell(1) != null) task.setType(getCellValue(r.getCell(1)));//孔性
                if (r.getCell(3) != null) task.setHeight(new BigDecimal(getCellValue(r.getCell(3))));//标高
                if (r.getCell(2) != null) task.setDepth(new BigDecimal(getCellValue(r.getCell(2)))); //勘探深度
                if (r.getCell(4) != null || r.getCell(5) != null)
                    task.setLonLat(getCellValue(r.getCell(4)) + "," + getCellValue(r.getCell(5)));
                if (r.getCell(6) != null || r.getCell(7) != null)
                    task.setTestLonLat(getCellValue(r.getCell(6)) + "," + getCellValue(r.getCell(7)));
                if (r.getCell(8) != null || r.getCell(9) != null)
                    task.setRelativeXY(getCellValue(r.getCell(8)) + "," + getCellValue(r.getCell(9)));
                if (r.getCell(10) != null) task.setSpacing(getCellValue(r.getCell(10)));
                if (r.getCell(11) != null) task.setDesc(getCellValue(r.getCell(11)));
                task.setProjectId(projectId);
                task.setDeleted(false);
                task.setName(task.getNo());
                task.setCreateAt(new Date());
                task.setStatus(DataStatus.TASK_STATUS_ASSIGN.getVal());
                temp.add(task);
            } else {
                break;
            }
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List importjicengExcel(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if (r.getRowNum() == 0) {
                continue;
            }
            //创建实体类
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            if (r.getCell(1) != null) jsonObject.put("jobNum", getCellValue(r.getCell(1)));
            if (r.getCell(2) != null) jsonObject.put("name", getCellValue(r.getCell(2)));
            if (r.getCell(3) != null) jsonObject.put("genter", getCellValue(r.getCell(3)));
            if (r.getCell(4) != null) jsonObject.put("qq", getCellValue(r.getCell(4)));
            if (r.getCell(5) != null) jsonObject.put("userName", getCellValue(r.getCell(5)));
            if (r.getCell(6) != null) jsonObject.put("depName", getCellValue(r.getCell(6)));
            if (r.getCell(7) != null) jsonObject.put("comName", getCellValue(r.getCell(7)));
            if (r.getCell(8) != null) jsonObject.put("phone", getCellValue(r.getCell(8)));
            if (r.getCell(9) != null) jsonObject.put("region", getCellValue(r.getCell(9)));
            if (r.getCell(10) != null) jsonObject.put("remark", getCellValue(r.getCell(10)));
            if (r.getCell(11) != null) jsonObject.put("professionalDomain", getCellValue(r.getCell(11)));

            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List importusercengExcel(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if (r.getRowNum() == 0) {
                continue;
            }
            //创建实体类
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            if (r.getCell(1) != null) jsonObject.put("depName", getCellValue(r.getCell(1)));
//            if (r.getCell(2) != null) jsonObject.put("userName", getCellValue(r.getCell(2)));
            if (r.getCell(3) != null) jsonObject.put("name", getCellValue(r.getCell(3)));
//            if (r.getCell(4) != null ) jsonObject.put("depName", getCellValue(r.getCell(4)));
            if (r.getCell(5) != null) jsonObject.put("phone", getCellValue(r.getCell(5)));
//            if (r.getCell(4) != null) jsonObject.put("region", getCellValue(r.getCell(4)));

            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List importhelpExcel(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if (r.getRowNum() == 0) {
                continue;
            }
            //创建实体类
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            if (r.getCell(0) != null) jsonObject.put("jobNum", getCellValue(r.getCell(0)));
            if (r.getCell(1) != null) jsonObject.put("depName", getCellValue(r.getCell(1)));
            if (r.getCell(2) != null) jsonObject.put("qq", getCellValue(r.getCell(2)));
            if (r.getCell(3) != null) jsonObject.put("name", getCellValue(r.getCell(3)));
            if (r.getCell(4) != null) jsonObject.put("phone", getCellValue(r.getCell(4)));
//            if (r.getCell(4) != null) jsonObject.put("region", getCellValue(r.getCell(4)));

            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    @Override
    public List importstatieExcel(InputStream inputStream) throws IOException {
        List temp = new ArrayList();
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new XSSFWorkbook(inputStream);
        Sheet sht0 = wb0.getSheetAt(0);
        for (Row r : sht0) {
            JSONObject jsonObject = new JSONObject();
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if (r.getRowNum() == 0) {
                continue;
            }
            //创建实体类
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            if (r.getCell(1) != null) jsonObject.put("jobNum", getCellValue(r.getCell(1)));
            if (r.getCell(2) != null) jsonObject.put("comName", getCellValue(r.getCell(2)));
            if (r.getCell(3) != null) jsonObject.put("name", getCellValue(r.getCell(3)));
            if (r.getCell(4) != null) jsonObject.put("phone", getCellValue(r.getCell(4)));
            if (r.getCell(5) != null) jsonObject.put("depName", getCellValue(r.getCell(5)));
//            if (r.getCell(4) != null) jsonObject.put("region", getCellValue(r.getCell(4)));

            temp.add(jsonObject);
        }
        inputStream.close();
        return temp;
    }

    public OutputStream exportExcel(String sheetName, List<Map> datas, ServletOutputStream outputStream) {

        // 创建一个workbook 对应一个excel应用文件
        XSSFWorkbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workBook.createSheet(sheetName);
        ExportUtil exportUtil = new ExportUtil(workBook, sheet);
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
        // 构建表头
        XSSFRow headRow = sheet.createRow(0);
        headRow.setHeightInPoints((short) 30);
        XSSFCell cell = null;
        if (datas.size() > 0) { //  订单数大于 0
            Map map0 = datas.get(0);
            Set set = map0.keySet();
            Iterator iterator = set.iterator();
            int i = 0;
            while (iterator.hasNext()) {
                cell = headRow.createCell(i++);
                cell.setCellStyle(headStyle);
                cell.setCellValue(iterator.next().toString());
            }
            // 构建表体数据
            for (int j = 0; j < datas.size(); j++) {
                XSSFRow bodyRow = sheet.createRow(j + 1);
                Map data = datas.get(j);
                Iterator iterator1 = data.values().iterator();
                int k = 0;
                bodyRow.setHeightInPoints((short) 28);
                while (iterator1.hasNext()) {
                    cell = bodyRow.createCell(k++);
                    cell.setCellStyle(bodyStyle);
                    Object o = iterator1.next();
                    if (o == null) {
                        o = "";
                    }
                    cell.setCellValue(o.toString());
                }
            }
            for (int e = 0; e < set.size(); e++) {
                sheet.autoSizeColumn(e);
            }
        }

        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void setResponseHeader(HttpServletResponse response, HttpServletRequest request, String fileName) {
        response.setContentType("application/binary;charset=UTF-8");
        fileName = Utils.processFileName(request, fileName);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment; filename=\"" + fileName + (new SimpleDateFormat("yyyy-MM-dd").format(new Date())) + ".xlsx\"");// 组装附件名称和格式
        response.setCharacterEncoding("UTF-8");
    }
}
